package table;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import common.Helper;
import database.DatabaseConnection;

/**
 * 处理评价相关的所有数据库操作的类
 */
public class EvalHandle {
	Connection conn;
	Statement stmt;
	ResultSet rs;
	
	/**
	 * 初始化连接
	 */
	private void initConnection() {
		try {
			long t1 = System.currentTimeMillis();
			conn = DatabaseConnection.getConnection();
			stmt = conn.createStatement();
			long last = System.currentTimeMillis() - t1;
			System.out.println("建立连接耗时: " + last + "ms");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public EvalHandle() {
		System.out.println("####EvalHandle创建####");
		initConnection();
	}
	
	@Override
	protected void finalize() throws Throwable {
		try {
			System.out.println("####EvalHandle销毁####");
			conn.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		super.finalize();
	}
	
	/**
	 * 获取所有评价活动信息
	 * @return 储存着评价活动信息的列表
	 */
	public List<EvaluationEvent> getAllEe(){
		ArrayList<EvaluationEvent> list = new ArrayList<>();
		try {
			if(conn == null) {
				
			}
			if(conn == null) initConnection();
			String sql = "SELECT * FROM tb_evalevent";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				int id = rs.getInt("ee_id");
				String name = rs.getString("ee_name");
				String info = rs.getString("ee_info");
				String status = rs.getString("ee_status");
				String startTime = rs.getString("ee_starttime");
				String endTime = rs.getString("ee_endTime");
				EvaluationEvent ee = new EvaluationEvent();
				ee.setId(id);
				ee.setName(name);
				ee.setInfo(info);
				ee.setStatus(status);
				ee.setStartTime(startTime);
				ee.setEndTime(endTime);
				list.add(ee);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 获取eeid中包含idScreen字符串的所有评价活动对象
	 * @return 储存着评价活动信息的列表
	 */
	public List<EvaluationEvent> getEesWithIdLike(String idScreen){
		ArrayList<EvaluationEvent> list = new ArrayList<>();
		try {
			if(conn == null) initConnection();
			String sql = "SELECT * FROM tb_evalevent WHERE ee_id like '%"+ idScreen + "%';";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				int id = rs.getInt("ee_id");
				String name = rs.getString("ee_name");
				String info = rs.getString("ee_info");
				String status = rs.getString("ee_status");
				String startTime = rs.getString("ee_starttime");
				String endTime = rs.getString("ee_endTime");
				EvaluationEvent ee = new EvaluationEvent();
				ee.setId(id);
				ee.setName(name);
				ee.setInfo(info);
				ee.setStatus(status);
				ee.setStartTime(startTime);
				ee.setEndTime(endTime);
				list.add(ee);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 获取指定小组参与的所有评价活动信息
	 * @param teamid 小组id
	 * @return 储存对应评价活动信息的列表
	 */
	public List<EvaluationEvent> getEesForTeam(int teamid){
		ArrayList<EvaluationEvent> list = new ArrayList<>();
		
		try {
			if(conn == null) initConnection();
			String sql = "SELECT * FROM tb_eeattendant WHERE eea_teamid='" + teamid + "';";
			rs = stmt.executeQuery(sql);
			List<Integer> idlist = new ArrayList<>();
			while(rs.next()) {
				idlist.add(rs.getInt("eea_eeid"));
			}
			
			for(int id : idlist) {
				sql = "SELECT * FROM tb_evalevent WHERE ee_id='" + id + "';";
				rs = stmt.executeQuery(sql);
				while(rs.next()) {
					int eeid = rs.getInt("ee_id");
					String name = rs.getString("ee_name");
					String info = rs.getString("ee_info");
					String status = rs.getString("ee_status");
					String startTime = rs.getString("ee_starttime");
					String endTime = rs.getString("ee_endTime");
					EvaluationEvent ee = new EvaluationEvent();
					ee.setId(eeid);
					ee.setName(name);
					ee.setInfo(info);
					ee.setStatus(status);
					ee.setStartTime(startTime);
					ee.setEndTime(endTime);
					list.add(ee);
				}
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 获得参与id为eeid的评价活动的所有小组信息
	 * @param eeid
	 * @return 参与活动的小组列表
	 */
	public List<Attendant> getAttendantsFromEe(int eeid){
		ArrayList<Attendant> list = new ArrayList<>();
		
		try {
			if(conn == null) initConnection();
			String sql = "SELECT a.eea_eeid,a.eea_teamid,b.team_name,b.team_info,a.eea_team_ename,a.eea_team_info,a.eea_team_selfeval FROM tb_eeattendant as a RIGHT JOIN tb_team as b ON a.eea_teamid=b.team_id WHERE eea_eeid='" + eeid + "' ORDER BY a.eea_teamid ASC;";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				Attendant att = new Attendant();
				att.setEeid(rs.getInt("eea_eeid"));
				att.setTeamid(rs.getInt("eea_teamid"));
				att.setTeamName(rs.getString("team_name"));
				att.setTeamInfo(rs.getString("team_info"));
				att.setEname(rs.getString("eea_team_ename"));
				att.setEinfo(rs.getString("eea_team_info"));
				att.setSelfeval(rs.getString("eea_team_selfeval"));
				list.add(att);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 获取指定评价活动中的所有评价
	 * @param eeid 评价活动id
	 * @return 对应评价活动中的所有评价
	 */
	public List<Evaluation> getAllEvalsFromEe(int eeid) {
		ArrayList<Evaluation> list = new ArrayList<>();
		try {
			if(conn == null) initConnection();
			String sql = "SELECT * FROM tb_evaluation WHERE eval_eeid='" + eeid + "' ORDER BY eval_lastedittime DESC;";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				Evaluation eval = new Evaluation();
				eval.setId(rs.getInt("eval_id"));
				eval.setEeid(rs.getInt("eval_eeid"));
				eval.setUserid(rs.getString("eval_userid"));
				eval.setTeamid(rs.getInt("eval_teamid"));
				eval.setScore(rs.getInt("eval_score"));
				eval.setRemark(rs.getString("eval_remark"));
				eval.setLastEditTime(rs.getString("eval_lastedittime"));
				if(eval.getScore() == 0) continue;
				list.add(eval);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 通过评价id获取指定的评价
	 * @param evalid 评价id
	 * @return 对应的评价
	 */
	public Evaluation getEvalById(int evalid) {
		Evaluation eval = null;
		try {
			if(conn == null) initConnection();
			String sql = "SELECT * FROM tb_evaluation WHERE eval_id='" + evalid + "'";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				Evaluation ev = new Evaluation();
				ev.setId(rs.getInt("eval_id"));
				ev.setEeid(rs.getInt("eval_eeid"));
				ev.setUserid(rs.getString("eval_userid"));
				ev.setTeamid(rs.getInt("eval_teamid"));
				ev.setScore(rs.getInt("eval_score"));
				ev.setRemark(rs.getString("eval_remark"));
				ev.setLastEditTime(rs.getString("eval_lastedittime"));
				eval = ev;
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return eval;
	}
	
	/**
	 * 通过eeid、userid和teamid获取对应的评价
	 * @param eeid 所属的评价活动id
	 * @param userid 执行评价的用户id
	 * @param teamid 被评价小组的id
	 * @return 对应的评价
	 */
	public Evaluation getEval(int eeid, String userid, int teamid) {
		Evaluation eval = null;
		try {
			if(conn == null) initConnection();
			String sql = "SELECT * FROM tb_evaluation WHERE eval_userid='" + userid + "' AND eval_eeid='" + eeid + "' AND eval_teamid='" + teamid + "'";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				Evaluation ev = new Evaluation();
				ev.setId(rs.getInt("eval_id"));
				ev.setEeid(rs.getInt("eval_eeid"));
				ev.setUserid(rs.getString("eval_userid"));
				ev.setTeamid(rs.getInt("eval_teamid"));
				ev.setScore(rs.getInt("eval_score"));
				ev.setRemark(rs.getString("eval_remark"));
				ev.setLastEditTime(rs.getString("eval_lastedittime"));
				eval = ev;
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return eval;
	}
	
	/**
	 * 通过eeid来获取对应评价活动
	 * @param eeid 评价活动id
	 * @return 指定评价活动
	 */
	public EvaluationEvent getEeById(int eeid) {
		EvaluationEvent ee = null;
		try {
			if(conn == null) initConnection();
			String sql = "SELECT * FROM tb_evalevent WHERE ee_id='" + eeid + "'";
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				EvaluationEvent evale = new EvaluationEvent();
				evale.setId(eeid);
				evale.setName(rs.getString("ee_name"));
				evale.setInfo(rs.getString("ee_info"));
				evale.setStatus(rs.getString("ee_status"));
				evale.setStartTime(rs.getString("ee_starttime"));
				evale.setEndTime(rs.getString("ee_endtime"));
				ee = evale;
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ee;
	}
	
	/**
	 * 获取某个评价活动中的所有对某个小组的评价
	 * @param teamid 小组id
	 * @param eeid 评价活动id
	 * @return 评价列表
	 */
	public List<Evaluation> getEvalsForTeamInEe(int teamid, int eeid){
		ArrayList<Evaluation> list = new ArrayList<>();
		try {
			if(conn == null) initConnection();
			String sql = "SELECT * FROM tb_evaluation WHERE eval_eeid='" + eeid + "' AND eval_teamid='" + teamid + "'";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				Evaluation eval = new Evaluation();
				eval.setId(rs.getInt("eval_id"));
				eval.setEeid(rs.getInt("eval_eeid"));
				eval.setUserid(rs.getString("eval_userid"));
				eval.setTeamid(rs.getInt("eval_teamid"));
				eval.setScore(rs.getInt("eval_score"));
				eval.setRemark(rs.getString("eval_remark"));
				eval.setLastEditTime(rs.getString("eval_lastEditTime"));
				list.add(eval);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 更新评价活动信息到数据库
	 * @param old_eeid 原评价活动id
	 * @param ee 需要更新成为的评价活动
	 * @return
	 */
	public boolean updateEeToDB(int old_eeid, EvaluationEvent ee) {
		boolean ret = false;
		try {
			if(conn == null) initConnection();
			String sql = String.format(
					"UPDATE tb_evalevent SET ee_id='%d', ee_name='%s', ee_info='%s', ee_status='%s', ee_starttime='%s', ee_endtime='%s' WHERE ee_id='" + old_eeid + "'",
					ee.getId(), ee.getName(), ee.getInfo(), ee.getStatus(), ee.getStartTime(), ee.getEndTime());
			int result = stmt.executeUpdate(sql);
			if(result != 0)
				ret = true;
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 更新评价信息到数据库
	 * @param old_evalid 原评价id
	 * @param eval 需要更新成为的评价
	 * @return
	 */
	public boolean updateEvalToDB(int old_evalid, Evaluation eval) {
		boolean ret = false;
		try {
			if(conn == null) initConnection();
			String sql = String.format(
					"UPDATE tb_evaluation SET eval_id='%d', eval_eeid='%d', eval_userid='%s', eval_teamid='%d', eval_score='%d', eval_remark='%s', eval_lastedittime='%s' WHERE eval_id='" + old_evalid + "'",
					eval.getId(), eval.getEeid(), eval.getUserid(), eval.getTeamid(), eval.getScore(), eval.getRemark(), eval.getLastEditTime());
			int result = stmt.executeUpdate(sql);
			if(result != 0)
				ret = true;
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 更新参与者信息到数据库
	 * @param old_eeid 原Attendant的eeid
	 * @param old_teamid 原Attendant的teamid
	 * @param att 需要更新成为的Attendant
	 * @return
	 */
	public boolean updateAttendantToDB(int old_eeid, int old_teamid, Attendant att) {
		boolean ret = false;
		try {
			if(conn == null) initConnection();
			String sql = String.format(
					"UPDATE tb_eeattendant SET eea_eeid='%d', eea_teamid='%d', eea_team_ename='%s', eea_team_info='%s', eea_team_selfeval='%s' WHERE eea_eeid='" + old_eeid + "' AND eea_teamid='" + old_teamid + "'",
										att.getEeid(), att.getTeamid(), att.getEname(), att.getEinfo(), att.getSelfeval());
			int result = stmt.executeUpdate(sql);
			if(result != 0)
				ret = true;
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 添加评价活动到数据库，如果ee.id=0，则自动生成编号
	 * @param ee 需要添加的评价活动
	 * @return 成功true，失败则false
	 */
	public boolean addEe(EvaluationEvent ee) {
		boolean ret = false;
		try {
			if(conn == null) initConnection();
			String id 		= "DEFAULT";
			String name 	= "'[新评价活动]'";
			String info 	= "''";
			String status 	= "DEFAULT";
			String stime 	= "NULL";
			String etime 	= "NULL";
			
			if(ee.getId() 			!= 0) 		id 		= "'" + ee.getId() 			+ "'";
			if(ee.getName() 		!= null) 	name 	= "'" + ee.getName() 		+ "'";
			if(ee.getInfo() 		!= null) 	info 	= "'" + ee.getInfo() 		+ "'";
			if(ee.getStatus() 		!= null) 	status 	= "'" + ee.getStatus() 		+ "'";
			if(ee.getStartTime() 	!= null) 	stime 	= "'" + ee.getStartTime() 	+ "'";
			if(ee.getEndTime() 		!= null) 	etime 	= "'" + ee.getEndTime() 	+ "'";
			
			String sql = String.format(
					"INSERT tb_evalevent VALUES(%s,%s,%s,%s,%s,%s);", id, name, info, status, stime, etime);
			int result = stmt.executeUpdate(sql);
			if(result != 0)
				ret = true;
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 添加评价到数据库，如果eval.id=0，则自动生成编号，如果lastEditTime=null，则自动生成当前时间
	 * @param eval 需要添加的评价
	 * @return 成功true，失败则false
	 */
	public boolean addEval(Evaluation eval) {
		if(eval.getEeid() == 0 || eval.getTeamid() == 0 || eval.getUserid() == null) {
			System.err.println("eeid/teamid/userid不能为空！");
			return false;
		}	
		
		boolean ret = false;
		try {
			if(conn == null) initConnection();
			String id 		= "DEFAULT";
			String eeid 	= "0";
			String uid 		= "0";
			String tid 		= "0";
			String score 	= "0";
			String remark 	= "''";
			String let		= "'" + Helper.dateToDBTime(new Date()) + "'";
			
			if(eval.getId() 		  != 0) 	id 		= "'" + eval.getId() 			+ "'";
			if(eval.getEeid() 		  != 0) 	eeid 	= "'" + eval.getEeid() 	 		+ "'";
			if(eval.getTeamid() 	  != 0) 	tid 	= "'" + eval.getTeamid() 		+ "'";
			if(eval.getScore() 	 	  != 0) 	score 	= "'" + eval.getScore()  		+ "'";
			if(eval.getUserid()		  != null) 	uid 	= "'" + eval.getUserid() 		+ "'";
			if(eval.getRemark() 	  != null) 	remark 	= "'" + eval.getRemark() 		+ "'";
			if(eval.getLastEditTime() != null)  let 	= "'" + eval.getLastEditTime()  + "'";
			
			String sql = String.format(
					"INSERT tb_evaluation VALUES(%s,%s,%s,%s,%s,%s,%s);", id, eeid, uid, tid, score, remark,let);
			int result = stmt.executeUpdate(sql);
			if(result != 0)
				ret = true;
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 向评价活动中添加小组
	 * @param eeid
	 * @param teamid
	 * @return
	 */
	public boolean addAttendantToEe(int eeid, int teamid) {
		boolean ret = false;
		try {
			if(conn == null) initConnection();
			String sql = "INSERT tb_eeattendant SET eea_eeid='" + eeid + "',eea_teamid='"+teamid+"',eea_team_info='';";
			int result = stmt.executeUpdate(sql);
			
			if(result != 0) {
				ret = true;
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 添加评价进入数据库
	 * @param eeid 所属的评价活动
	 * @param fromUserid 进行评价的用户的id
	 * @param toTeamid 被评价的小组的id
	 * @param score 评价的分数
	 * @return 成功true，失败false
	 */
	public boolean addEval(int eeid, String fromUserid, int toTeamid, int score) {
		return addEval(eeid, fromUserid, toTeamid, score, null);
	}
	
	/**
	 * 添加评价进入数据库
	 * @param eeid 所属的评价活动
	 * @param fromUserid 进行评价的用户的id
	 * @param toTeamid 被评价的小组的id
	 * @param score 评价的分数
	 * @param remark 评价的文字内容
	 * @return 成功true，失败false
	 */
	public boolean addEval(int eeid, String fromUserid, int toTeamid, int score, String remark) {
		Evaluation e = new Evaluation();
		e.setEeid(eeid);
		e.setUserid(fromUserid);
		e.setTeamid(toTeamid);
		e.setScore(score);
		e.setRemark(remark);
		return addEval(e);
	}
	
	/**
	 * 删除评价活动
	 * @param eeid 被删除评价活动的id
	 * @return 成功true，失败false
	 */
	public boolean deleteEeById(int eeid) {
		try {
			if(conn == null) initConnection();
			String sql = "DELETE FROM tb_evalevent WHERE ee_id='" + eeid + "';";
			int result = stmt.executeUpdate(sql);
			if(result == 0) return false;
			
			// 删除评价活动同时会清除所有该活动的参与者和所有相关评价
			sql = "DELETE FROM tb_eeattendant WHERE eea_eeid='" + eeid + "';";
			stmt.executeUpdate(sql);
			sql = "DELETE FROM tb_evaluation WHERE eval_eeid='" + eeid + "';";
			stmt.executeUpdate(sql);
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return true;
	}
	
	/**
	 * 删除Attendant
	 * @param eeid 参与者的所属的评价活动id
	 * @param teamid 参与的小组的id
	 * 
	 */
	public boolean deleteAttendant(int eeid, int teamid) {
		try {
			if(conn == null) initConnection();
			String sql = "DELETE FROM tb_eeattendant WHERE eea_eeid='" + eeid + "' AND eea_teamid='" + teamid + "';";
			int result = stmt.executeUpdate(sql);
			if(result == 0) return false;	
			
			// 删除参与者同时会清除该小组有关的所有评价
			deleteEvalsFromTeam(eeid, teamid);
			deleteEvalsToTeam(eeid, teamid);		

		}catch(Exception e) {
			e.printStackTrace();
		}
		return true;
	}
	
	/**
	 * 删除所有对某个小组的评价
	 * @param eeid 所属评价活动id
	 * @param teamid 小组id
	 * @throws SQLException 
	 */
	private void deleteEvalsToTeam(int eeid, int teamid) throws SQLException {
		if(conn == null) initConnection();
		String sql = "DELETE FROM tb_evaluation WHERE eval_eeid='" + eeid + "' AND eval_teamid='" + teamid + "';";
		stmt.executeUpdate(sql);
	}

	/**
	 * 删除某个小组对其他组的所有评价
	 * @param eeid 所属评价活动id
	 * @param teamid 小组id
	 * @throws SQLException 
	 */
	private void deleteEvalsFromTeam(int eeid, int teamid) throws SQLException {
		if(conn == null) initConnection();
		String sql = "DELETE FROM tb_evaluation WHERE eval_eeid='" + eeid + "' AND eval_userid IN (SELECT user_id FROM tb_user WHERE user_teamid='" + teamid + "');";
		stmt.executeUpdate(sql);
	}

	/**
	 * 使用评价id删除评价
	 * @param evalid 评价的id
	 * @return 成功true，失败false
	 */
	public boolean deleteEvalById(int evalid) {
		boolean ret = false;
		try {
			if(conn == null) initConnection();
			String sql = "DELETE FROM tb_evaluation WHERE eval_id='" + evalid + "';";
			int result = stmt.executeUpdate(sql);
			
			if(result != 0) {
				ret = true;
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 使用eeid,userid,teamid删除评价
	 * @param eeid 评价所属的评价活动id
	 * @param userid 进行评价的用户的id
	 * @param teamid 被评价的小组的id
	 * @return 成功true，失败false
	 */
	public boolean deleteEval(int eeid, String userid, int teamid) {
		boolean ret = false;
		try {
			if(conn == null) initConnection();
			String sql = "DELETE FROM tb_evaluation WHERE eval_eeid='" + eeid + "' AND eval_userid='" + userid + "' AND eval_teamid='" + teamid + "';";
			int result = stmt.executeUpdate(sql);
			
			if(result != 0) {
				ret = true;
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 通过teamid和eeid获取指定的Attendant
	 * @param teamid 小组id
	 * @param eeid 评价活动id
	 * @return 指定的Attendant
	 */
	public Attendant getAttendant(int teamid, int eeid) {
		Attendant ret = null;
		try {
			if(conn == null) initConnection();
			String sql = "SELECT a.eea_eeid,a.eea_teamid,b.team_name,b.team_info,a.eea_team_ename,a.eea_team_info,a.eea_team_selfeval FROM tb_eeattendant as a RIGHT JOIN tb_team as b ON a.eea_teamid=b.team_id WHERE a.eea_eeid='" + eeid + "' AND a.eea_teamid='" + teamid + "';";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				Attendant att = new Attendant();
				att.setEeid(rs.getInt("eea_eeid"));
				att.setTeamid(rs.getInt("eea_teamid"));
				att.setTeamName(rs.getString("team_name"));
				att.setTeamInfo(rs.getString("team_info"));
				att.setEname(rs.getString("eea_team_ename"));
				att.setEinfo(rs.getString("eea_team_info"));
				att.setSelfeval(rs.getString("eea_team_selfeval"));
				ret = att;
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
	
	/**
	 * 获取指定评价活动中指定用户的所有评价，包括数据库没有的但实际上应该有的评价。
	 * @param userid 用户id
	 * @param eeid 评价活动id
	 * @return 指定的Evaluation列表
	 */
	public List<Evaluation> getEvalsOfUserInEe(String userid, int eeid) {
		ArrayList<Evaluation> list = new ArrayList<>();
		try {
			if(conn == null) initConnection();
			// 查询对应userid的小组id
			String sql = "SELECT * FROM tb_user WHERE user_id='" + userid + "';";
			rs = stmt.executeQuery(sql);
			int user_teamid = 0;
			while(rs.next()) {
				user_teamid = rs.getInt("user_teamid");
			}
			
			// 查询对应的评价活动有那些小组参加
			sql = "SELECT * FROM tb_eeattendant as a INNER JOIN tb_team as b ON a.eea_teamid=b.team_id WHERE a.eea_eeid='" + eeid + "';";
			rs = stmt.executeQuery(sql);
			List<Integer> teamids = new ArrayList<>();
			while(rs.next()) {
				int tmp = rs.getInt("eea_teamid");
				if(tmp != user_teamid) {
					teamids.add(tmp);
				}
			}
			
			// 查询对应的userid和eeid有多少条评价
			sql = "SELECT * FROM tb_eeattendant as c LEFT JOIN tb_evaluation as a ON a.eval_eeid=c.eea_eeid AND a.eval_teamid=c.eea_teamid RIGHT JOIN tb_team as b ON c.eea_teamid=b.team_id WHERE eea_eeid='" + eeid + "' AND eval_userid='" + userid + "' ORDER BY eea_teamid ASC;";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				Evaluation eval = new Evaluation();
				eval.setId(rs.getInt("eval_id"));
				eval.setEeid(rs.getInt("eval_eeid"));
				eval.setUserid(rs.getString("eval_userid"));
				eval.setTeamid(rs.getInt("eval_teamid"));
				eval.setScore(rs.getInt("eval_score"));
				eval.setRemark(rs.getString("eval_remark"));
				eval.setLastEditTime(rs.getString("eval_lastedittime"));
				
				eval.setEname(rs.getString("eea_team_ename"));
				eval.setEinfo(rs.getString("eea_team_info"));
				eval.setTeamName(rs.getString("team_name"));
				
				list.add(eval);
			}
			
			// 如果评价数不够，则添加评价重新获取
			if(list.size() < teamids.size()) {
				for(int tid : teamids) {
					boolean flag = false;
					for(Evaluation e : list) if(e.getTeamid() == tid) flag = true;
					if(!flag) { // 如果不存在则添加空白记录
						Evaluation evalToAdd = new Evaluation();
						evalToAdd.setEeid(eeid);
						evalToAdd.setUserid(userid);
						evalToAdd.setTeamid(tid);
						addEval(evalToAdd);
					}
				}
			}
			list.clear();
			sql = "SELECT * FROM tb_eeattendant as c LEFT JOIN tb_evaluation as a ON a.eval_eeid=c.eea_eeid AND a.eval_teamid=c.eea_teamid RIGHT JOIN tb_team as b ON c.eea_teamid=b.team_id WHERE eea_eeid='" + eeid + "' AND eval_userid='" + userid + "' ORDER BY eea_teamid ASC;";
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				Evaluation eval = new Evaluation();
				eval.setId(rs.getInt("eval_id"));
				eval.setEeid(rs.getInt("eval_eeid"));
				eval.setUserid(rs.getString("eval_userid"));
				eval.setTeamid(rs.getInt("eval_teamid"));
				eval.setScore(rs.getInt("eval_score"));
				eval.setRemark(rs.getString("eval_remark"));
				eval.setLastEditTime(rs.getString("eval_lastedittime"));
				
				eval.setEname(rs.getString("eea_team_ename"));
				eval.setEinfo(rs.getString("eea_team_info"));
				eval.setTeamName(rs.getString("team_name"));
				
				list.add(eval);
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 批量更新评价信息到数据库
	 * @param evalList
	 * @return 返回成功更新的个数
	 */
	public int updateEvalsToDB(List<Evaluation> evalList) {
		int ret = 0;
		try {
			if(conn == null) initConnection();
			for(Evaluation e : evalList) {
				if(updateEvalToDB(e.getId(), e))
					ret ++;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return ret;
	}
}
